from concurrent.futures import ThreadPoolExecutor
from sentinelsat import SentinelAPI
from xml.dom.minidom import parse
import tkinter as tk
from queue import Queue
import pathlib
import psycopg2
import datetime
import zipfile
import glob
import copy
import re
import os

# 获取1c含云量
def read_cloud(zipFilePath):
	""""""
	xmlFilePathRe = "^S2.*\.SAFE/MTD_MSIL1C\.xml$"
	zipFile = zipfile.ZipFile(zipFilePath, "r")
	zipFileInfos = zipFile.infolist()
	for zipFileInfo in zipFileInfos:
		if re.match(xmlFilePathRe, zipFileInfo.filename):
			xmlFilePath = zipFile.open(zipFileInfo.filename, "r")
			domTree = parse(xmlFilePath)
			collection = domTree.documentElement
			cloudPercentElement = collection.getElementsByTagName("Cloud_Coverage_Assessment")[0]
			cloudPercent = round(float(copy.deepcopy(cloudPercentElement.childNodes[0].data)))
	return cloudPercent

# 主动入库
def zip_import_sql(path):
	folder = r"/home/遥感技术部/遥感数据/Sentinel2L1cZip"
	name = os.path.basename(path)
	if not os.path.exists(path):
		print(f"{datetime.datetime.now()}>{name}:入库失败!")
		return
	size = os.path.getsize(path)
	size = round(size/(1024*1024),2)
	infos = os.path.splitext(name)[0].split("_")
	shot = datetime.datetime.strptime(infos[2], "%Y%m%dT%H%M%S")
	load = datetime.datetime.strptime(infos[6], "%Y%m%dT%H%M%S")
	tile = infos[5][1:]
	cloud = read_cloud(path)
	date = shot.strftime("%Y%m%d")
	path = str(pathlib.PurePosixPath(folder, date, name))
	sql = f'''
			INSERT INTO l1c_zip_info (name, level, clouds, tile, size, path, shot_date, load_date) 
			VALUES ('{name}', 'L1C', {cloud}, '{tile}', {size}, '{path}', '{shot}', '{load}')
			on conflict (path) do nothing
		'''   # 字段唯一值冲突则不更新
	try:
		con = psycopg2.connect(
			database="sentinel2",
			user="postgres",
			password="12345",
			host="192.168.2.172",
			port="5432"
		)
		cur = con.cursor()	
		cur.execute(sql)
		con.commit()
		cur.close()
		con.close()
		string = f"{path}:入库成功!"
	except:
		string = f"{path}:入库失败!"
	return string

# 清临时数据
def delete_incomplete(args):
	tiles, start_date, end_date, folder = args
	start_date = datetime.datetime.strptime(start_date,"%Y-%m-%d")
	end_date = datetime.datetime.strptime(end_date,"%Y-%m-%d")
	while start_date != (end_date+datetime.timedelta(days=1)):
		date = start_date.strftime("%Y%m%d")
		for tile in tiles:
			name_re = f"S2*_{date}T*_T{tile}_*.zip.incomplete"
			path_re = os.path.join(folder, date, name_re)
			for path in glob.glob(path_re):
				print(f"{datetime.datetime.now()}>删除：{path}")
				os.remove(path)	
		start_date = start_date + datetime.timedelta(days=1)
	return

# 下载哨兵数据
def update_sentinel2_l1c(args):
	[product, folder, users, userq] = args
	[product_id, product_date, product_name] = product
	date_name = product_date.strftime("%Y%m%d")
	date_dir = os.path.join(folder, date_name)
	if not os.path.exists(date_dir):
		os.makedirs(date_dir)
	temp_name = f"{product_name}.zip.incomplete"
	temp_path = os.path.join(date_dir, temp_name)
	if os.path.exists(temp_path):
		os.remove(temp_path)
		# info = f"删除文件：{temp_path}"
		# out_print(info, out_info)
	count = 0
	while count != 5:
		user_index = userq.get(block=True)
		[username, password] = users[user_index]
		try:
			api=SentinelAPI(username,password,timeout=60)
			api.download(product_id,directory_path=date_dir)
			info=f"{product_name}:第{count+1}次下载成功!"
			print(info)
			# out_print(info, out_info)
			name = f"{product_name}.zip"
			path = os.path.join(date_dir, name)
			info = zip_import_sql(path)
			# out_print(info, out_info)
			print(info)
			count, product_name = 5, None
		except:
			if os.path.exists(temp_path):
				os.remove(temp_path) 
			count += 1
			info = f"{product_name}第{count}次下载失败！"
			print(info)
			# out_print(info, out_info)
		userq.put(user_index, block=True)	
	return product_name

# 检索esa数据
def search_sentinel2_l1c(args):
	[tile,start_date,end_date,maxcloud,users,userq]=args
	# [tile,start_date,end_date,maxcloud,users,userq,out_info]=args
	start_date=datetime.datetime.strptime(start_date,"%Y-%m-%d")
	end_date=datetime.datetime.strptime(end_date,"%Y-%m-%d")
	count, products = 0, []
	while count != 5:
		user_index = userq.get(block=True)
		[username, password] = users[user_index]
		try:
			api = SentinelAPI(username, password, timeout=60)
			query_kwargs = {
				'platformname': 'Sentinel-2',
				'producttype': 'S2MSI1C',
				'date': (start_date, end_date),
				"cloudcoverpercentage": (0, maxcloud),
				"raw": f'tileid:{tile} OR filename:*_T{tile}_*',
				"tileid": tile}
			for product_id in api.query(**query_kwargs):
				if api.is_online(product_id):
					product_info=api.get_product_odata(product_id)
					product_date=product_info["date"]
					product_name=product_info['title']
					product=[product_id,product_date,product_name]
					products.append(product)
				else:
					info=f"{product_name}:离线数据!"
					print(info)
					# out_print(info, out_info)
					pass
			info = f"第{count+1}次查询成功：{tile}，{len(products)}幅！"
			print(info)
			# out_print(info, out_info)
			count = 5
		except:
			count += 1
			info = f"第{count}次查询失败：{tile}！"
			print(info)
			# out_print(info, out_info)
		userq.put(user_index, block=True)
	return products

# get sentinel2 tile list
def get_province_tiles(province):
	con = psycopg2.connect(
	database="sentinel2",
	user="postgres",
	password="12345",
	host="192.168.2.172",
	port="5432"
	)
	cur = con.cursor()
	sql = f"""
	SELECT "中国有效哨兵格网分布wgs84".mgrs
	FROM china_sy
	JOIN "中国有效哨兵格网分布wgs84" 
	on st_intersects(china_sy.geom,"中国有效哨兵格网分布wgs84".geom)
	WHERE  china_sy.qhmc = '{province}' 
	"""
	cur.execute(sql)
	con.commit()
	tiles=[record[0] for record in cur.fetchall()]
	tiles = [tile.upper() for tile in tiles]
	cur.close()
	con.close()
	return tiles

# get sentinel2 tile list
def get_city_tiles(province, city):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_dsy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_dsy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_dsy.qhmc = '{city}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_sy
        WHERE  china_sy.qhmc = '{province}' 
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

# get sentinel2 tile list
def get_county_tiles(province, city, county):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_xy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_xy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_xy.qhmc = '{county}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_dsy
        WHERE qhmc = '{city}'
        AND sjxzqdm in (
            SELECT qhdm
            FROM china_sy
            WHERE china_sy.qhmc = '{province}'
        )  
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

def out_print(info, out_info):
	string = f"\n{datetime.datetime.now()}>{info}"
	out_info.insert(tk.END, string)
	out_info.see(tk.END)
	out_info.update()
	return

# 主程序
def main(tiles, start_date, end_date,max_cloud,folder,txt_path,out_info):
	
	info=f"多进程 esa 下载数据!"
	out_print(info, out_info)
	info=f"注意："
	out_print(info, out_info)
	info=f"1：需要登录VPN翻墙软件，否则下载会失败！"
	out_print(info, out_info)
	info=f"2：需要映射172目录，否则入库会失败！"
	out_print(info, out_info)
	
	if txt_path == "":
		users = [
			["fyy001", "show1234"],
			["fyy_01", "show1234"],
			["fyy_02", "show1234"],
			["fyy_03", "show1234"],
			["fyy_04", "show1234"],
			["hxx2021", "hxx2021123"],
			["hxx2022", "hxx2022123"],
			["hxx20221", "hxx2022123"],
			["hxx20222", "hxx20222123"],
			["wyq01", "show970213"],
			["wyq02", "show970213"],
			["wyq03", "show970213"],
			["wyq04", "show970213"],
			["wyq05", "show970213"],
			["wyq06", "show970213"],
			["wyq07", "show970213"],
		]
	else:
		with open(txt_path, "r",encoding='utf-8') as file:
			text = file.readlines()
		users = [
			line.replace("\n","").split(",")
			for line in text]
	userq = Queue(maxsize=len(users))
	for i in range(len(users)):
		userq.put(i)

	out_print(f"图幅个数：{len(tiles)}", out_info)
	out_print(f"查询esa发布的1C数据!", out_info)

	args_list = [
		[tile,start_date,end_date,max_cloud,users,userq]
		for tile in tiles]
	with ThreadPoolExecutor(max_workers=int(len(users)-4)) as executor:
		results = executor.map(search_sentinel2_l1c,args_list)
	out_print("开始下载哨兵1C数据！", out_info)
	args_list = [
		[product,folder,users,userq] 
		for result in results for product in result]
	out_print(f"共计下载：{len(args_list)}幅！", out_info)
	with ThreadPoolExecutor(max_workers=int(len(users)-4)) as executor:
		results = set(executor.map(update_sentinel2_l1c,args_list))
	out_print(f"下载失败列表：\n{results}", out_info)
	out_print(f"清除未下载完成的数据！", out_info)
	delete_incomplete([tiles, start_date, end_date, folder])
	out_print(f"END！", out_info)
	return

# ui
class my_ui:

	default_folder = "Z:\\Sentinel2L1cZip"

	def __init__(self):
		# 定义窗口
		self.window = tk.Tk()
		self.window.title('Sentinel-2数据（L1C）下载工具（ESA）')
		self.window.geometry('800x1000')

		#定义一个lable
		self.tilestr_label = tk.Label(
			self.window,
			text="请输入图幅列表",
			font = ('Helvetica', '14', 'bold'),
			width=15,
			height=2)
		#定义一个输入框entry
		self.tilestr_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			font = ('Helvetica', '14', 'bold'),
			textvariable="",
			selectforeground = 'red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.tilestr_label.grid(column=0, row=0)   # grid dynamically divides the space in a grid
		self.tilestr_text.grid(column=1, row=0)   # and arranges widgets accordingly

		#定义一个lable
		self.province_label = tk.Label(
			self.window,
			text="请输入省名称",
			font = ('Helvetica', '14', 'bold'),
			width=15,
			height=2)
		#定义一个输入框entry
		self.province_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			textvariable="",
			font = ('Helvetica', '14', 'bold'),
			selectforeground = 'red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.province_label.grid(column=0, row=1)   # grid dynamically divides the space in a grid
		self.province_text.grid(column=1, row=1)   # and arranges widgets accordingly

		#定义一个lable
		self.city_label = tk.Label(
			self.window,
			text="请输入市名称",
			font = ('Helvetica', '14', 'bold'),
			width=15,
			height=2)
		#定义一个输入框entry
		self.city_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			textvariable="",
			font = ('Helvetica', '14', 'bold'),
			insertwidth = 3,
			selectforeground ='red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.city_label.grid(column=0, row=2)   # grid dynamically divides the space in a grid
		self.city_text.grid(column=1, row=2)   # and arranges widgets accordingly

		#定义一个lable
		self.county_label = tk.Label(
			self.window,
			text="请输入县名称",
			font = ('Helvetica', '14', 'bold'),
			width=15,height=2)
		#定义一个输入框entry
		self.county_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			textvariable="",
			font = ('Helvetica', '14', 'bold'),
			selectforeground ='red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.county_label.grid(column=0, row=3)   # grid dynamically divides the space in a grid
		self.county_text.grid(column=1, row=3)   # and arranges widgets accordingly

		#定义一个lable
		self.start_date_label = tk.Label(
			self.window,
			text="请输入起始日期",
			font = ('Helvetica', '14', 'bold'),
			width=15,
			height=3)
		#定义一个输入框entry
		now_date = datetime.datetime.now()
		now_date = now_date.strftime("%Y-%m-%d")
		self.start_date_default_value = tk.StringVar()
		self.start_date_default_value.set(now_date)
		self.start_date_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			textvariable=self.start_date_default_value,
			font = ('Helvetica', '14', 'bold'),
			selectforeground ='red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.start_date_label.grid(column=0, row=4)   # grid dynamically divides the space in a grid
		self.start_date_text.grid(column=1, row=4)   # and arranges widgets accordingly

		#定义一个lable
		self.end_date_label = tk.Label(
			self.window,
			text="请输入截至日期",
			font = ('Helvetica', '14', 'bold'),
			width=15,
			height=3)
		now_date = datetime.datetime.now()
		now_date = now_date+datetime.timedelta(days=1)
		now_date = now_date.strftime("%Y-%m-%d")
		self.end_date_default_value = tk.StringVar()
		self.end_date_default_value.set(now_date)
		#定义一个输入框entry
		self.end_date_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			textvariable=self.end_date_default_value,
			font = ('Helvetica', '14', 'bold'),
			selectforeground ='red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.end_date_label.grid(column=0, row=5)   # grid dynamically divides the space in a grid
		self.end_date_text.grid(column=1, row=5)   # and arranges widgets accordingly

		#定义一个lable
		self.max_cloud_label = tk.Label(
			self.window,
			text="请输入最大云量",
			font=('Helvetica', '14', 'bold'),
			width=15,
			height=3)
		self.max_cloud_default_value = tk.StringVar()
		self.max_cloud_default_value.set("100")
		#定义一个输入框entry
		self.max_cloud_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			textvariable=self.max_cloud_default_value,
			font = ('Helvetica', '14', 'bold'),
			selectforeground ='red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.max_cloud_label.grid(column=0, row=6)   # grid dynamically divides the space in a grid
		self.max_cloud_text.grid(column=1, row=6)   # and arranges widgets accordingly

		#定义一个lable
		self.folder_path_label = tk.Label(
			self.window,
			text="请输入网络映射目录",
			font=('Helvetica', '14', 'bold'),
			width=15,
			height=3)
		self.folder_path_value = tk.StringVar()
		self.folder_path_value.set(self.default_folder)
		#定义一个输入框entry
		self.folder_path_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			textvariable=self.folder_path_value,
			font = ('Helvetica', '14', 'bold'),
			selectforeground ='red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.folder_path_label.grid(column=0, row=7)   # grid dynamically divides the space in a grid
		self.folder_path_text.grid(column=1, row=7)   # and arranges widgets accordingly

		#定义一个lable
		self.text_path_label = tk.Label(
			self.window,
			text="请输入配置文件路径",
			font=('Helvetica', '14', 'bold'),
			width=15,
			height=3)
		self.text_path_value = tk.StringVar()
		self.text_path_value.set("")
		#定义一个输入框entry
		self.text_path_text=tk.Entry(
			self.window,
			width=40,
			background = 'white',
			borderwidth = 3,
			relief = 'sunken',
			insertwidth = 3,
			textvariable=self.text_path_value,
			font = ('Helvetica', '14', 'bold'),
			selectforeground ='red',
			selectbackground = 'blue',
			selectborderwidth = 3,
			show=None)
		self.text_path_label.grid(column=0, row=8)   # grid dynamically divides the space in a grid
		self.text_path_text.grid(column=1, row=8)   # and arranges widgets accordingly

		#定义一个按钮
		self.button_1=tk.Button(
			self.window,
			text="开始下载",
			font=('Helvetica', '14', 'bold'),
			width=15,
			height=2,
			command=self.run_main)
		self.button_1.grid(column=1, row=9)   # and arranges widgets accordingly
		
		#  输出结果
		Output_results = tk.Label(
			self.window, 
			text='运行状态：',
			font=('Helvetica', '14', 'bold'),
			)
		Output_results.grid(column=0,row=10,sticky='NW')
		self.result_data_Text = tk.Text(
			self.window, 
			# state = 'disabled',
			width=60, 
			height=200)  #处理结果展示
		self.result_data_Text.grid(
			column=1, 
			row=11, 
			rowspan=15, 
			columnspan=10)

		self.window.mainloop()

	def run_main(self):
		tilestr = self.tilestr_text.get()
		if tilestr != "":
			tiles = [tile.upper() for tile in tilestr.split("-")]
		else:
			province = self.province_text.get()
			city = self.city_text.get()
			if city !="":
				county = self.county_text.get()
				if county != "":
					tiles = get_county_tiles(province, city, county)
				else:
					tiles = get_city_tiles(province, city)
			else:
				tiles = get_province_tiles(province)
		start_date = self.start_date_text.get()
		end_date = self.end_date_text.get()

		max_cloud = self.max_cloud_text.get()
		txt_path = self.text_path_text.get()
		folder_path=self.folder_path_text.get()
		self.result_data_Text.insert(tk.END, '开启下载')
		self.result_data_Text.see(tk.END)
		self.result_data_Text.update()
		
		main(
			tiles, 
			start_date, 
			end_date,
			max_cloud,
			folder=folder_path,
			txt_path=txt_path,
			out_info=self.result_data_Text)

		return
		
if __name__ == "__main__":
	command=r"net use Z: \\192.168.2.172\root DELL123dell /user:root"
	try:
		os.system(command)
	except:
		pass
	my_ui()
	